﻿
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqLiteHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Forms;

namespace Dwg_output
{
    class Wire_ExceltoSQL
    {
        private string _Path;
        private string _PathExcel;
        private string _PathDb;

       

        public static string ceshi="";
        private int excel_jigui = 18;
        private int excel_SAFE_RELAY_NUM = 19;
        private int excel_SAFE_RELAY = 8;
        private int excel_base = 16;
        private int excel_base_jigui = 10;
        private int excel_base_Sys_jigui = 10;
        private int excel_signal_Positive = 25;
        private int excel_signal_Negative = 26;
        private int excel_signal_Termail = 29;

        public Wire_ExceltoSQL(string path)
        {
            _Path = path;

        }
        public Wire_ExceltoSQL(string pathExcel, string pathDb)
        {
            _PathExcel = pathExcel;
            _PathDb = pathDb;

        }


        private bool isFileExDb()
        {
            if (File.Exists(_PathExcel))
                return true;
            else
                return false;

        }
        public int writeExceltoSqlite()
        {
            if (false == isFileExDb())
            {
                MessageBox.Show("选择的导入表格丢失丢失");
                return 0;
            }

            //新建数据库
            SQLiteHelper buildDB = new SQLiteHelper(_PathDb, "");

            string deleteBIAO = @"   DROP TABLE IF EXISTS terminal;                             
                                     DROP TABLE IF EXISTS tagInfo;
                                     DROP TABLE IF EXISTS jigui;
                                     DROP TABLE IF EXISTS sys_jigui;
                                    ";

            string project = @"CREATE TABLE project_base (id INTEGER PRIMARY KEY
                                , xiangmu TEXT,   kehu TEXT, project_order TEXT, contract TEXT, system TEXT
                                , designer TEXT,  checker TEXT, audit TEXT
                                , document TEXT,  description TEXT,date TEXT,vsion TEXT)";

            string jigui = @"CREATE TABLE jigui (id INTEGER PRIMARY KEY,name TEXT NOT NULL , model TEXT, controlstation_id INTEGER,leve3_id INTEGER)";
            string sys_jigui = @"CREATE TABLE sys_jigui (id INTEGER PRIMARY KEY,name TEXT NOT NULL , model TEXT, controlstation_id INTEGER,leve3_id INTEGER)";
            string Taginfo = @"CREATE TABLE tagInfo (id INTEGER PRIMARY KEY
                                , tag_name TEXT NOT NULL, description TEXT, sign_type TEXT, power_supply_status TEXT
                                , area_id INTEGER, control_id INTEGER,card_id INTEGER, card_type INTEGER
                                , sign_flow_code TEXT, io_sign_type TEXT, configuration TEXT,base TEXT,addres TEXT
                                ,Terminal_board TEXT,board_num TEXT,termial TEXT, spd TEXT,spd_num TEXT
                                ,sbsi TEXT,sbsi_num TEXT ,signal_power TEXT)";



            buildDB.buildEXcel(deleteBIAO);
           // buildDB.buildEXcel(project);
            buildDB.buildEXcel(jigui);
            buildDB.buildEXcel(sys_jigui);
            int error1 =  buildDB.addDate(Taginfo);
            return error1;

         }

        /// <summary>
        /// 读取项目信息
        /// 
        /// </summary>
        /// <param name="_PathExcel"></param>
        /// <param name="_PathDb"></param>
        public int read_Project(string _PathExcel, string _PathDb)

        {
            int resSave2 = 0;
            //读取用户信息
            using (FileStream fs = File.Open(_PathExcel, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))//打开文件
            {


                ExcelHelper excelHelper = new ExcelHelper();
                IWorkbook wk = ExcelHelper.ReadFromExcel(_PathExcel);
                if (wk==null)
                {
                    MessageBox.Show("表格文件被打开");
                    return 0;
                }
                //_Sheet = _Work.GetSheet(sheetName); //按照表明方式获取sheet
                ISheet sheet = wk.GetSheetAt(1);


                // 连接数据库
                SQLiteHelper sqlhelper = new SQLiteHelper(_PathDb, ""); //参数1: db的绝对路径 参数2: 数据库密码
                string sql_inst = "INSERT INTO project_base (xiangmu,kehu,project_order,vsion,date,designer,checker,audit) VALUES (@xiangmu,@kehu,@project_order,@vsion,@date,@designer,@checker,@audit)";




                // 通过行和列索引获取单元格（Cell）对象    
                ICell cell_xiangmu = sheet.GetRow(4).GetCell(4);
                ICell cell_kehu = sheet.GetRow(5).GetCell(4);
                ICell cell_order = sheet.GetRow(3).GetCell(12);

                ICell cell_Rev = sheet.GetRow(22).GetCell(0);
                ICell cell_date = sheet.GetRow(22).GetCell(1);
                ICell cell_design = sheet.GetRow(22).GetCell(3);
                ICell cell_check = sheet.GetRow(22).GetCell(5);
                ICell cell_approve = sheet.GetRow(22).GetCell(7);

                // 获取单元格内容    
                string cellValueXiangmu = cell_xiangmu.ToString();
                string cellValueKehu = cell_kehu.ToString();
                string cellValueOrder = cell_order.ToString();
                string cellValueRev = cell_Rev.ToString();
                string cellValueDate = cell_date.ToString();
              
                string cellValueDesign = cell_design.ToString();
                string cellValueCheck = cell_check.ToString();
                string cellValueApprove = cell_approve.ToString();

                // 将所有的单元格值添加到列表中    
                List<string> table = new List<string>();
                table.Add(cellValueXiangmu);
                table.Add(cellValueKehu);
                table.Add(cellValueOrder);
                table.Add(cellValueRev);
                table.Add(cellValueDate);
                table.Add(cellValueDesign);
                table.Add(cellValueCheck);
                table.Add(cellValueApprove);


                SQLiteParameter[] param_Tag = new SQLiteParameter[]{
                     new SQLiteParameter("@xiangmu", table[0].ToString()),
                       new SQLiteParameter("@kehu", table[1].ToString()),
                         new SQLiteParameter("@project_order", table[2].ToString()),
                            new SQLiteParameter("@vsion", table[3].ToString()),
                              new SQLiteParameter("@date", table[4].ToString()),
                                 new SQLiteParameter("@designer", table[5].ToString()),
                                     new SQLiteParameter("@checker", table[6].ToString()),
                                       new SQLiteParameter("@audit", table[7].ToString()),

                 };
                //保存到数据库执行
                 resSave2 = sqlhelper.addDate(sql_inst, param_Tag);

             
                //Console.WriteLine(sheet.LastRowNum);


            }
            return resSave2;

        }

        /// <summary>
        /// 读取机柜信息
        /// </summary>
        /// <param name="_PathExcel"></param>
        /// <param name="_PathDb"></param>
        public int read_Jigui(string _PathExcel, string _PathDb)
        {
            int resSave2 = 0;
            //读取用户信息
            using (FileStream fs = File.Open(_PathExcel, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))//打开文件
            {

               
                ExcelHelper excelHelper = new ExcelHelper();
                IWorkbook wk = ExcelHelper.ReadFromExcel(_PathExcel);
                //_Sheet = _Work.GetSheet(sheetName); //按照表明方式获取sheet
                ISheet sheet = wk.GetSheetAt(0);
                // 计算公式并获取结果  
                IFormulaEvaluator evaluator = wk.GetCreationHelper().CreateFormulaEvaluator();

                // 连接数据库
                SQLiteHelper sqlhelper = new SQLiteHelper(_PathDb, ""); //参数1: db的绝对路径 参数2: 数据库密码
                string sql_inst = "INSERT INTO jigui (name) VALUES (@name)";
                List<string> table = new List<string>();


                //第一个机柜开始检测，每当遇到null或者空格跳过，当下一个值不相同时，
                //把机柜记录到list中，并且把代码唯一值插入到TAG表中
                string previousValue = "";
             //   string previousValue2 = "";
                int i = 0;
                for (int r = 1; r <= sheet.LastRowNum; r++)
                {
                    //获取工作表中的每一行
                    IRow ccurrentRow = sheet.GetRow(r);
                    if (ccurrentRow ==null ||ccurrentRow.ToString()=="")
                    {
                        continue;
                    }//行为空则跳过
                    ICell transferCell = ccurrentRow.GetCell(18);//获取当前行的第一个单元格


                    //如果当前没有转接机柜则，查看转接基座是否为转接段子基座
                    if (transferCell == null || transferCell.ToString() == "")//如果转接机柜没有
                    {
                        ICell baseCell = ccurrentRow.GetCell(excel_base);
                    
                        if (baseCell ==null || baseCell.ToString()==""){continue;}
                        string base_str = read_excel_Formula(baseCell, evaluator);

                        if (base_str.Equals("B-IO9011") || base_str.Equals("B-IO9001"))//且基座为转接端子
                        {

                            ICell currentJigui_cell = ccurrentRow.GetCell(excel_base_jigui);
                            string currentJigui_str = read_excel_Formula(currentJigui_cell, evaluator);

                            if (currentJigui_str != previousValue)
                            {
                                previousValue = currentJigui_str;
                                i++;
                                table.Add(currentJigui_str);

                            }



                        }
                        else
                        {
                            continue;
                        }


                    }

                    else//否则为常规转接机柜
                    {
                        string currentJigui = read_excel_Formula(transferCell, evaluator);
                     

                        if (currentJigui != previousValue)
                        {
                            previousValue = currentJigui;
                            i++;
                            table.Add(currentJigui);

                        }

                    }

                }
               
                for (int j = 0; j < i; j++)
                {
                    //查询是不是有相同数据
                    string querySql = "SELECT COUNT(*) FROM jigui WHERE name = @name";
                    SQLiteParameter[] queryParams = new SQLiteParameter[]{
                              new SQLiteParameter("@name", table[j].ToString())
                      };
                    int count = Convert.ToInt32(sqlhelper.ExecuteScalar(querySql, queryParams));

                    if (count>0)
                    {
                        continue;
                    }


                    //没有相同机柜时，则存储这个机柜的信息
                    SQLiteParameter[] param_Tag = new SQLiteParameter[]{
                             new SQLiteParameter("@name", table[j].ToString()),


                             };
                    //保存到数据库执行
                    resSave2 = sqlhelper.addDate(sql_inst, param_Tag);


                    //Console.WriteLine(sheet.LastRowNum);
                }
              

            }
            return resSave2;


        }
        public int read_sys_Jigui(string _PathExcel, string _PathDb)
        {
            int resSave2 = 0;
            //读取用户信息
            using (FileStream fs = File.Open(_PathExcel, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))//打开文件
            {


                ExcelHelper excelHelper = new ExcelHelper();
                IWorkbook wk = ExcelHelper.ReadFromExcel(_PathExcel);
                //_Sheet = _Work.GetSheet(sheetName); //按照表明方式获取sheet
                ISheet sheet = wk.GetSheetAt(0);
                // 计算公式并获取结果  
                IFormulaEvaluator evaluator = wk.GetCreationHelper().CreateFormulaEvaluator();

                // 连接数据库
                SQLiteHelper sqlhelper = new SQLiteHelper(_PathDb, ""); //参数1: db的绝对路径 参数2: 数据库密码
                string sql_inst = "INSERT INTO sys_jigui (name) VALUES (@name)";
                List<string> table = new List<string>();
              

                //第一个机柜开始检测，每当遇到null或者空格跳过，当下一个值不相同时，
                //把机柜记录到list中，并且把代码唯一值插入到TAG表中
                string previousValue = "";
              
                int i = 0;
                for (int r = 1; r <= sheet.LastRowNum; r++)
                {
                    //获取工作表中的每一行
                    IRow ccurrentRow = sheet.GetRow(r);
                    if (ccurrentRow == null || ccurrentRow.ToString() == "")
                    {
                        continue;
                    }//行为空则跳过

                    //////////////////////////////////////////////
                    ICell ccurrentCell = ccurrentRow.GetCell(excel_base_jigui);//获取当前行的第一个单元格
                    if (ccurrentCell == null || ccurrentCell.ToString() == "") { continue; }
                    string currentValue = read_excel_Formula(ccurrentCell, evaluator);

                    if (currentValue != previousValue)
                        {
                            previousValue = currentValue;
                            i++;
                            table.Add(currentValue);

                        }

                   

                }

                for (int j = 0; j < i; j++)
                {
                    //查询是不是有相同数据
                    string querySql = "SELECT COUNT(*) FROM sys_jigui WHERE name = @name";
                    SQLiteParameter[] queryParams = new SQLiteParameter[]{
                              new SQLiteParameter("@name", table[j].ToString())
                      };
                    int count = Convert.ToInt32(sqlhelper.ExecuteScalar(querySql, queryParams));

                    if (count > 0)
                    {
                        continue;
                    }


                    //没有相同机柜时，则存储这个机柜的信息
                    SQLiteParameter[] param_Tag = new SQLiteParameter[]{
                             new SQLiteParameter("@name", table[j].ToString()),


                             };
                    //保存到数据库执行
                    resSave2 = sqlhelper.addDate(sql_inst, param_Tag);
                    //Console.WriteLine(sheet.LastRowNum);
                }


            }
            return resSave2;


        }

        /// <summary>
        /// 读取位号
        /// </summary>
        /// <param name="_PathExcel"></param>
        /// <param name="_PathDb"></param>
        /// <returns></returns>
        public int read_Tag(Form1_WireDwg formReference,string _PathExcel, string _PathDb)
        {
            int bi = 0;
            Dictionary<int, string> sys_jigui = new Dictionary<int, string>();
            sys_jigui = Wire_ReadSQL.Read_Sys_Jigui_Dictionary(_PathDb, 1);
            //先读取//
            using (FileStream fs = File.Open(_PathExcel, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))//打开文件
            {


                ExcelHelper excelHelper = new ExcelHelper();
                IWorkbook wk = ExcelHelper.ReadFromExcel(_PathExcel);
                //_Sheet = _Work.GetSheet(sheetName); //按照表明方式获取sheet
                ISheet sheet = wk.GetSheetAt(0);


                // 连接数据库
                SQLiteHelper sqlhelper = new SQLiteHelper(_PathDb, ""); //参数1: db的绝对路径 参数2: 数据库密码
                string sql_inst = @"INSERT INTO tagInfo (tag_name, description, sign_type, configuration,io_sign_type,area_id,card_id,addres,base,Terminal_board,board_num,control_id,signal_power,sbsi ,sbsi_num ,termial) 
                                    VALUES  (@tag_name, @description, @sign_type,@configuration,@io_sign_type,@area_id,@card_id,@addres,@base,@Terminal_board,@board_num,@control_id,@signal_power,@sbsi ,@sbsi_num,@termial)";
                //  string sql_inst = "INSERT INTO tagInfo (tag_name) VALUES (@tag_name)";


                int i = 0;

                // 计算公式并获取结果  
                IFormulaEvaluator evaluator = wk.GetCreationHelper().CreateFormulaEvaluator();//放在循环里面可以大幅增加读取时间O。0
                List<string> jiguiStringID = new List<string>();

                for (int r = 1; r <= sheet.LastRowNum; r++)
                {
                        //获取工作表中的每一行
                        IRow ccurrentRow = sheet.GetRow(r);
                        if (ccurrentRow == null || ccurrentRow.ToString() == "")
                        {
                            i++;  continue;
                        }//行为空则跳过

                        ICell currrentCellTag = ccurrentRow.GetCell(0);//获取当前行的第一个单元格
                        if (currrentCellTag == null || currrentCellTag.ToString() == "")
                        { i++; continue; }//遇到空行则标记一次



                        List<string> table = new List<string>();
                        string previousValue = "";
                        //遍历一整行内容
                        for (int c = 0; c < 33; c++)
                        {
                            // ccurrentRow.LastCellNum
                            //获取每个单元格
                            ICell currrentCell = ccurrentRow.GetCell(c);
                            if (currrentCell == null)//如果为空值null则返回
                            {
                                table.Add("");

                            }


                            else if (currrentCell.CellType == CellType.Formula)//是公式产生的值，则为这个进入函数
                            {
                                // 创建公式求值器  
                                CellValue cellValue = evaluator.Evaluate(currrentCell);
                                string result;
                                if (cellValue.CellType == CellType.Numeric)
                                {
                                     result = cellValue.NumberValue.ToString();
                                }

                                else if (cellValue.CellType == CellType.String)
                                {
                              
                                     result = cellValue.StringValue.ToString();
                              
                                }
                          
                                else
                                {
                                     result = "Unknown cell value type";
                                }
                                    table.Add(result);

                            }
                            else//非公式产生的值
                            {

                                string result = currrentCell.ToString();
                                table.Add(result ?? "");
                               // ceshi = result;
                            }

                        }

                        //获取所有机柜机柜，并输出标识
                  
                        jiguiStringID = read_db_jigui( _PathDb, table, previousValue);
                        int memberId = read_sys_jigui_id(table[excel_base_Sys_jigui], sys_jigui);//字典查询




                         SQLiteParameter[] param_Tag = new SQLiteParameter[]{
                                   new SQLiteParameter("@tag_name", table[4].ToString()),
                                   new SQLiteParameter("@description", table[5].ToString()),
                                   new SQLiteParameter("@sign_type", table[6].ToString()),
                                   new SQLiteParameter("@configuration", table[9].ToString()),
                                   new SQLiteParameter("@io_sign_type", table[7].ToString()),
                                   new SQLiteParameter("@addres", table[13].ToString()+"|"+ table[14].ToString()),
                                   new SQLiteParameter("@base", table[16].ToString()),
                                   new SQLiteParameter("@Terminal_board", table[21].ToString()),
                                   new SQLiteParameter("@board_num", table[20].ToString()),
                                   new SQLiteParameter("@card_id", i),
                                   new SQLiteParameter("@area_id", int.Parse(jiguiStringID[0])),
                                   new SQLiteParameter("@control_id", memberId),
                                   new SQLiteParameter("@signal_power", table[excel_signal_Positive].ToString()+"|"+table[excel_signal_Negative].ToString()),
                                   new SQLiteParameter("@sbsi", table[excel_SAFE_RELAY].ToString()),
                                   new SQLiteParameter("@sbsi_num", table[excel_SAFE_RELAY_NUM].ToString()),
                                   new SQLiteParameter("@termial", table[excel_signal_Termail].ToString()),
                                 };

                    
                         //保存到数据库执行
                         int resSave2 = sqlhelper.addDate(sql_inst, param_Tag);
                         bi = resSave2;

                    try
                    {
                        formReference.Invoke(new Action(() =>
                        {
                            formReference.ProgressBar1.Maximum = sheet.LastRowNum;
                            formReference.ProgressBar1.Value = r;
                            formReference.label.Text = "进度:" + r + "/" + sheet.LastRowNum;
                        }));
                    }
                    catch (ObjectDisposedException)
                    {
                        // 处理已经销毁的窗体对象异常
                        // 这里可以不做任何操作，或者添加日志记录等处理方式
                    }





                }
               
                Console.WriteLine(sheet.LastRowNum);
                return bi;

            }


        }


        private string read_excel_Formula(ICell currrentCell, IFormulaEvaluator evaluator)
        {
            string result;

            if (currrentCell == null)//如果为空值null则返回
            {
                result = "";

            }


            else if (currrentCell.CellType == CellType.Formula)//是公式产生的值，则为这个进入函数
            {
                // 创建公式求值器  
                CellValue cellValue = evaluator.Evaluate(currrentCell);
                if (cellValue.CellType == CellType.Numeric)
                {
                    result = cellValue.NumberValue.ToString();
                }
                else if (cellValue.CellType == CellType.String)
                {
                    result = cellValue.StringValue.ToString();
                }
                else
                {
                    result = "Unknown cell value type";
                }
              

            }
            else//非公式产生的值
            {

                result = currrentCell.ToString();
                if (result == null)
                {
                    result = "";
                }

            }
            return result;


        }




        private int read_sys_jigui_id(string memberName, Dictionary<int, string> sys_jigui)
        {

            return  sys_jigui.FirstOrDefault(x => x.Value.Contains(memberName)).Key;
           
        }


        private List<string> read_db_jigui( string _PathDb, List<string> table, string previousValue)
        {

            //如果没有转接机柜，那么这个所在的系统机柜也单独成一个机柜名称
            //查询读取到的机柜值，是否再别的表中存在，如果存在则这个表对应这个机柜的值赋值
         
          
            // 上一次迭代的单元格值
            List<string> jiguiStringID = new  List<string>();
            if (previousValue.Equals(""))
            {
                jiguiStringID.Add("999");
            }
            if (table[excel_jigui].ToString() != null && table[excel_jigui].ToString() != "")//有转接机柜时
            {


                if (table[excel_jigui].ToString() != previousValue)
                {

                    previousValue = table[excel_jigui].ToString();//标记值赋值
                    string jiguiID = string.Format("SELECT id FROM jigui WHERE name = '{0}'", previousValue);
                    SQLiteHelperV2 id_value = new SQLiteHelperV2(_PathDb);
                    jiguiStringID = id_value.getDBValues(jiguiID);

                }
                

            }

            else//转接段子基座添加标记
            {
                if (table[excel_base].Equals("B-IO9011")|| table[excel_base].Equals("B-IO9001"))//为转接基座时
                {
                    if (table[excel_base_jigui].ToString() != previousValue)//不等于上一个机柜的值
                    {
                        previousValue = table[excel_base_jigui].ToString();//标记值赋值
                        string jiguiID = string.Format("SELECT id FROM jigui WHERE name = '{0}'", previousValue);
                        SQLiteHelperV2 id_value = new SQLiteHelperV2(_PathDb);
                        jiguiStringID = id_value.getDBValues(jiguiID);
                    }

                }



            }

            return jiguiStringID;



        }



        private List<string> read_db_sys_jigui(string _PathDb, List<string> table, string previousValue)
        {

            //如果没有转接机柜，那么这个所在的系统机柜也单独成一个机柜名称
            //查询读取到的机柜值，是否再别的表中存在，如果存在则这个表对应这个机柜的值赋值

            // 上一次迭代的单元格值
            List<string> jiguiString_sys_ID = new List<string>();
            if (table[excel_base].ToString() != null && table[excel_base].ToString() != "")//有转接机柜时
            {


                if (table[excel_jigui].ToString() != previousValue)
                {

                    previousValue = table[excel_jigui].ToString();//标记值赋值
                    string jiguiID = string.Format("SELECT id FROM jigui WHERE name = '{0}'", previousValue);
                    SQLiteHelperV2 id_value = new SQLiteHelperV2(_PathDb);
                    jiguiString_sys_ID = id_value.getDBValues(jiguiID);

                }


            }


            return jiguiString_sys_ID;



        }
       

        //判断信号流
        //信号流过元器件情况
        //io-sign-type类型  AI  AO  AI-TC  AI-RTD等
        //card-type 


        public class ExcelHelper
        {
            /// <summary>
            /// 从Excel读取数据，只支持单表
            /// </summary>
            /// <param name="FilePath">文件路径</param>
            public static IWorkbook ReadFromExcel(string FilePath)
            {
                IWorkbook wk = null;
                try
                {
                 
                    string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名

                    using (FileStream fs = File.OpenRead(FilePath))
                    {
                        if (extension.Equals(".xls")) //2003
                        {
                            wk = new HSSFWorkbook(fs);
                        }
                        else                         //2007以上
                        {
                            wk = new XSSFWorkbook(fs);
                        }
                    }

                    //读取当前表数据
                    ISheet sheet = wk.GetSheetAt(0);
                    //构建DataTable
                    IRow row = sheet.GetRow(0);
                    return wk;
                }
                catch (Exception x)
                {

                    MessageBox.Show(x.ToString());
                }

                return wk;


            }
        }

    }
}

